package merlin.managers;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import merlin.beans.EmailDataBean;
import merlin.beans.IWCustomerDetailsBean;
import merlin.beans.IWRegistrationBean;
import merlin.beans.ReportTitlesBean;
import merlin.beans.WebsiteOrdersBean;
import merlin.dao.ConnectionPool;
import merlin.dao.IndustryWatchDAO;
import merlin.dao.SystemConfigurationDAO;
import merlin.dao.WebsiteEnquiriesDAO;
import merlin.dao.WebsiteOrdersDAO;
import merlin.dao.WebsiteReportOrdersDAO;
import merlin.dao.WebsiteReportRequestsDAO;
import merlin.utils.ApplicationResources;
import merlin.utils.CreditCardTransaction;
import merlin.utils.MailSender;
import merlin.utils.MerlinUtilities;
import merlin.utils.PDFReportGenerator;

public class WebsiteEnquiriesManager{

	public static int addWebsiteEnquiry(String strFirstName,String strSurname,String strCompanyName,String strEMailAddress,String strTelephone,int[] arrReportIds){
		Connection con=null;
		ConnectionPool pool = null;

		int enquiryId = -1;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			con.setAutoCommit(false);
			WebsiteEnquiriesDAO wedao = new WebsiteEnquiriesDAO(con);
			enquiryId = wedao.addWebsiteEnquiry(strFirstName,strSurname,strCompanyName,strEMailAddress,strTelephone);
			WebsiteReportRequestsDAO wrrdao = new WebsiteReportRequestsDAO(con);
			int arr[] = wrrdao.addWebsiteReportRequests(enquiryId,arrReportIds);
			con.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
			return enquiryId;
		}
	}

	public static Map addWebsiteOrder(WebsiteOrdersBean woBean){
		Connection con=null;
		ConnectionPool pool = null;

		Exception ex = null;
		int orderId = -1;

		Map mapResponse = null;

		try{

			String[] arrRawReports = woBean.getRawReports();

			int arrReportIds[] = new int[arrRawReports.length/2];
			for(int i=0,j=0;i<arrReportIds.length;i++,j+=2){
				arrReportIds[i] = Integer.parseInt(arrRawReports[j]);

			}

			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			con.setAutoCommit(false);
			WebsiteOrdersDAO wedao = new WebsiteOrdersDAO(con);
			orderId = wedao.addWebsiteOrder(woBean);
			WebsiteReportOrdersDAO wrrdao = new WebsiteReportOrdersDAO(con);
			int arr[] = wrrdao.addWebsiteReportOrders(orderId,arrReportIds);

			mapResponse = CreditCardTransaction.doTransaction(woBean,orderId,new HashMap(),0);

			// Process the response from the ProtX site

			String returnCode = (String)mapResponse.get("Status");
			String vpsTxID = null, vpsSecurityCode = null;

			if("OK".equals(returnCode)){
				// transaction done properly. Update the database details & go to the success page
				mapResponse.put("OrderId",String.valueOf(orderId));
				vpsTxID = (String)mapResponse.get("VPSTxId");
				vpsSecurityCode = (String)mapResponse.get("SecurityKey");
  				// Update the database record with the TxID and SecKey.
				int updateCount = wedao.updateWebsiteOrder(orderId,vpsTxID,vpsSecurityCode);
				con.commit();
			}else if("ERROR".equals(returnCode) || "MALFORMED".equals(returnCode)){
				// some fault has occured due to malformed request message or from the server-end.
				// but save the data in the database.
				con.commit();
			}else if("NOTAUTHED".equals(returnCode) || "REJECTED".equals(returnCode) || "INVALID".equals(returnCode)){
				// Some problem in the data entered by the client. Rollback the order details.
				con.rollback();
			}

		}catch(Exception e){
			e.printStackTrace();
			ex = e;
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		if(ex != null){
			throw new RuntimeException(ex);
		}
		return mapResponse;
	}


	public static Map addIWOrder(IWRegistrationBean iwRegBean){
		Connection con=null;
		ConnectionPool pool = null;

		Exception ex = null;
		int orderId = -1;

		Map mapResponse = null;
		try{
	        ApplicationResources resource = ApplicationResources.getInstance();

			// break the selected reports list.

			String separatorPattern = resource.getProperty("separatorPattern");
			String arrRawReports[] = null;
			int arrReportIds[] = null;
			boolean cdDatabaseRequestedIndicator = false;
			boolean bookRequestedIndicator = false;
			boolean eBookRequestIndicator = false;


			if("T".equals(iwRegBean.getChkMerlinReports())){
				// reports will be available only if the user selects Merlin Scott reports.
				arrRawReports = iwRegBean.getHidSelectedReports().split(separatorPattern);

				arrReportIds = new int[arrRawReports.length/2];
				for(int i=0,j=0;i<arrReportIds.length;i++,j+=2){
					arrReportIds[i] = Integer.parseInt(arrRawReports[j]);
				}

				// report types are relevant only for Merlin Scott reports
				cdDatabaseRequestedIndicator = ("Book(s) and database(s)".equals(iwRegBean.getRadReportType())) || ("Searchable Database(s) only".equals(iwRegBean.getRadReportType()));
				bookRequestedIndicator = ("Book(s) and database(s)".equals(iwRegBean.getRadReportType())) || ("Book(s) only".equals(iwRegBean.getRadReportType()));
				eBookRequestIndicator = "eBook".equals(iwRegBean.getRadReportType());
			}

			WebsiteOrdersBean woBean = new WebsiteOrdersBean(0,iwRegBean.getTxtTitle(),iwRegBean.getTxtFirstName(),iwRegBean.getTxtSurname(),iwRegBean.getTxtPosition(),
												iwRegBean.getTxtCompanyName(),iwRegBean.getTxtAddress1(),iwRegBean.getTxtAddress2(),iwRegBean.getTxtTown(),iwRegBean.getTxtCounty(),
												iwRegBean.getTxtPostcode(),iwRegBean.getTxtTelephone(),iwRegBean.getTxtEMailAddress(),iwRegBean.getTxtCardNumber(),iwRegBean.getTxtCVV(),
												iwRegBean.getTxtExpiryDate(),cdDatabaseRequestedIndicator,iwRegBean.getTxtCardTitle(),
												iwRegBean.getTxtCardForename(),iwRegBean.getTxtCardSurname(),iwRegBean.getTxtCardPosition(),iwRegBean.getTxtCardCompanyName(),
												iwRegBean.getTxtCardAddress1(),iwRegBean.getTxtCardAddress2(),iwRegBean.getTxtCardTown(),iwRegBean.getTxtCardCounty(),
												iwRegBean.getTxtCardPostcode(),bookRequestedIndicator,iwRegBean.getTxtInvoiceTotal(),iwRegBean.getLstCardType(),
												iwRegBean.getRadReportType(),"F".equals(iwRegBean.getRadSameDetails()),arrRawReports,"A".equals(iwRegBean.getChkIndustryWatch()) || "B".equals(iwRegBean.getChkIndustryWatch()) || "C".equals(iwRegBean.getChkIndustryWatch()),eBookRequestIndicator);

			woBean.setStartDate(iwRegBean.getTxtStartDate());
			woBean.setIssueNumber(iwRegBean.getTxtIssueNumber());
			woBean.setDiscountCode(iwRegBean.getTxtDiscountCode());
			woBean.setPdfReportPurchase(iwRegBean.isPdfDownloadPurchase());
			if(iwRegBean.isPdfDownloadPurchase()){
				woBean.setNumPdfReports(Integer.parseInt(iwRegBean.getNumReportsToPurchase()));
				woBean.setPdfReportIds(iwRegBean.getHidReportIds());
    		}

			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			con.setAutoCommit(false);
			WebsiteOrdersDAO wedao = new WebsiteOrdersDAO(con);
			orderId = wedao.addWebsiteOrder(woBean);
			System.out.println("added WebsiteOrder");

			if(arrReportIds != null){ // if Merlin Scott reports are selected
				WebsiteReportOrdersDAO wrrdao = new WebsiteReportOrdersDAO(con);
				int arr[] = wrrdao.addWebsiteReportOrders(orderId,arrReportIds);

				//if the user has purchased Merlin e-books, then make entries in the pdf download history table too.
				if(eBookRequestIndicator){
					IndustryWatchDAO iwdao = new IndustryWatchDAO(con);
					String[] uuids = iwdao.addMerlinEBookDownloadHistory(orderId,arrReportIds);
				}
				System.out.println("Added Merlin reports");
			}

			if("A".equals(iwRegBean.getChkIndustryWatch()) || "B".equals(iwRegBean.getChkIndustryWatch()) || "C".equals(iwRegBean.getChkIndustryWatch())){	// only if IW reports are selected, make an entry
				Calendar calToday = new GregorianCalendar();
				// For subscription types A and B, the expiry date is 365 days from date of subscription.
				// For type C , it is 7 days.
				if("C".equals(iwRegBean.getChkIndustryWatch())){
					calToday.add(Calendar.DATE,7);
				}else{
					calToday.add(Calendar.DATE,365);
				}
				java.util.Date expiryDate = calToday.getTime();
       			IWCustomerDetailsBean iwCusBean = new IWCustomerDetailsBean(0,iwRegBean.getTxtEMailAddress(),expiryDate,orderId,iwRegBean.getTxtCompanyName());
       			iwCusBean.setFirstName(iwRegBean.getTxtFirstName());
       			iwCusBean.setSurname(iwRegBean.getTxtSurname());
       			iwCusBean.setTelephoneNumber(iwRegBean.getTxtTelephone());
       			iwCusBean.setSubscriptionType(iwRegBean.getChkIndustryWatch());

       			IndustryWatchDAO iwdao = new IndustryWatchDAO(con);
       			if(iwRegBean.isReRegistration()){
	       			int count = iwdao.updateIWCustomerDetails(iwCusBean);
				}else{
	       			int count = iwdao.addIWCustomerDetails(iwCusBean);
				}
       			System.out.println("added IW reports");
			}

			// PDF credit purchase.
   			if(iwRegBean.isPdfDownloadPurchase()){
   				IndustryWatchDAO iwdao = new IndustryWatchDAO(con);
				iwdao.updatePdfCreditsPurchase(iwRegBean.getTxtEMailAddress(),Integer.parseInt(iwRegBean.getNumReportsToPurchase()),orderId);
   			}

			// create the Map containing the detailed breakdown of prices.

			Map mapData = new HashMap();
			mapData.put("WebsiteOrdersBean",woBean);
			mapData.put("iwRegBean",iwRegBean);
			mapData.put("merlinReportIds", arrReportIds);

			Map mapPrices = MerlinUtilities.calculatePrices(mapData);
			mapPrices.put("iwRegBean",iwRegBean);
			mapResponse = CreditCardTransaction.doTransaction(woBean,orderId,mapPrices,iwRegBean.getHidDiscountAmount());

			// Test implementation.
            //mapResponse = CreditCardTransaction.doTransaction();

			// Process the response from the ProtX site

			String returnCode = (String)mapResponse.get("Status");
			String vpsTxID = null, vpsSecurityCode = null;
			String invoiceNumber = null;

			if("OK".equals(returnCode)){
				// transaction done properly. Update the database details & go to the success page
				mapResponse.put("OrderId",String.valueOf(orderId));
				vpsTxID = (String)mapResponse.get("VPSTxId");
				vpsSecurityCode = (String)mapResponse.get("SecurityKey");
  				// Update the database record with the TxID and SecKey.
				int updateCount = wedao.updateWebsiteOrder(orderId,vpsTxID,vpsSecurityCode);
				//get a new invoice number
				SystemConfigurationDAO scdao = new SystemConfigurationDAO(con);
				invoiceNumber = scdao.getLastInvoiceNumber();
				// increment it
				invoiceNumber = MerlinUtilities.getNewInvoiceId(invoiceNumber);
				scdao.saveInvoiceNumber(invoiceNumber);
				con.commit();
				// send mail to confirm the order.
				sendMailConfirmingOrder(woBean,orderId,iwRegBean,invoiceNumber,mapPrices);
			}else if("ERROR".equals(returnCode) || "MALFORMED".equals(returnCode)){
				// some fault has occured due to malformed request message or from the server-end.
				// but save the data in the database.
				con.commit();
			}else if("NOTAUTHED".equals(returnCode) || "REJECTED".equals(returnCode) || "INVALID".equals(returnCode)){
				// Some problem in the data entered by the client. Rollback the order details.
				con.rollback();
			}else if("3DAUTH".equals(returnCode)){
				// 3D-Secure authentiction , store the "MD" value in the table.
				String mdValue = (String)mapResponse.get("MD");
				int updateCount = wedao.save3DSecureMDValue(orderId,mdValue);
				con.commit();
				iwRegBean.setOrderId(orderId);  // store it for future use during callback.
			}


		}catch(Exception e){
			e.printStackTrace();
			ex = e;
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		if(ex != null){
			throw new RuntimeException(ex);
		}

		return mapResponse;
	}

	/**
	 * handle the callback from the issuing bank for a 3d secure transaction.
	 */
	public static Map handle3DCallback(IWRegistrationBean iwRegBean,String MDValue,String PAResValue){

		Exception ex = null;

		Map mapResponse = null;

		Connection con=null;
		ConnectionPool pool = null;

		try{
	        ApplicationResources resource = ApplicationResources.getInstance();

			mapResponse =  CreditCardTransaction.do3DSecureCallback(MDValue,PAResValue);

			String returnCode = (String)mapResponse.get("Status");
			String vpsTxID = null, vpsSecurityCode = null;
			String invoiceNumber = null;

			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			con.setAutoCommit(false);
			WebsiteOrdersDAO wedao = new WebsiteOrdersDAO(con);
			WebsiteReportOrdersDAO wrrdao = new WebsiteReportOrdersDAO(con);
   			IndustryWatchDAO iwdao = new IndustryWatchDAO(con);

			if("OK".equals(returnCode)){
				// break the selected reports list.
				String separatorPattern = resource.getProperty("separatorPattern");
				String arrRawReports[] = null;
				int arrReportIds[] = null;
				boolean cdDatabaseRequestedIndicator = false;
				boolean bookRequestedIndicator = false;
				boolean eBookRequestIndicator = false;

				if("T".equals(iwRegBean.getChkMerlinReports())){
					// reports will be available only if the user selects Merlin Scott reports.
					arrRawReports = iwRegBean.getHidSelectedReports().split(separatorPattern);

					arrReportIds = new int[arrRawReports.length/2];
					for(int i=0,j=0;i<arrReportIds.length;i++,j+=2){
						arrReportIds[i] = Integer.parseInt(arrRawReports[j]);
					}

					// report types are relevant only for Merlin Scott reports
					cdDatabaseRequestedIndicator = ("Book(s) and database(s)".equals(iwRegBean.getRadReportType())) || ("Searchable Database(s) only".equals(iwRegBean.getRadReportType()));
					bookRequestedIndicator = ("Book(s) and database(s)".equals(iwRegBean.getRadReportType())) || ("Book(s) only".equals(iwRegBean.getRadReportType()));
                    eBookRequestIndicator = "eBook".equals(iwRegBean.getRadReportType());
				}

				WebsiteOrdersBean woBean = new WebsiteOrdersBean(0,iwRegBean.getTxtTitle(),iwRegBean.getTxtFirstName(),iwRegBean.getTxtSurname(),iwRegBean.getTxtPosition(),
													iwRegBean.getTxtCompanyName(),iwRegBean.getTxtAddress1(),iwRegBean.getTxtAddress2(),iwRegBean.getTxtTown(),iwRegBean.getTxtCounty(),
													iwRegBean.getTxtPostcode(),iwRegBean.getTxtTelephone(),iwRegBean.getTxtEMailAddress(),iwRegBean.getTxtCardNumber(),iwRegBean.getTxtCVV(),
													iwRegBean.getTxtExpiryDate(),cdDatabaseRequestedIndicator,iwRegBean.getTxtCardTitle(),
													iwRegBean.getTxtCardForename(),iwRegBean.getTxtCardSurname(),iwRegBean.getTxtCardPosition(),iwRegBean.getTxtCardCompanyName(),
													iwRegBean.getTxtCardAddress1(),iwRegBean.getTxtCardAddress2(),iwRegBean.getTxtCardTown(),iwRegBean.getTxtCardCounty(),
													iwRegBean.getTxtCardPostcode(),bookRequestedIndicator,iwRegBean.getTxtInvoiceTotal(),iwRegBean.getLstCardType(),
													iwRegBean.getRadReportType(),"F".equals(iwRegBean.getRadSameDetails()),arrRawReports,"T".equals(iwRegBean.getChkIndustryWatch()),eBookRequestIndicator);

				woBean.setStartDate(iwRegBean.getTxtStartDate());
				woBean.setIssueNumber(iwRegBean.getTxtIssueNumber());
				woBean.setDiscountCode(iwRegBean.getTxtDiscountCode());

				// create the Map containing the detailed breakdown of prices.

				Map mapData = new HashMap();
				mapData.put("WebsiteOrdersBean",woBean);
				mapData.put("iwRegBean",iwRegBean);

				Map mapPrices = MerlinUtilities.calculatePrices(mapData);

				// transaction done properly. Update the database details & go to the success page
				vpsTxID = (String)mapResponse.get("VPSTxId");
				vpsSecurityCode = (String)mapResponse.get("SecurityKey");
				// Update the database record with the TxID and SecKey.
				int updateCount = wedao.updateWebsiteOrder(iwRegBean.getOrderId(),vpsTxID,vpsSecurityCode);
				//get a new invoice number
				SystemConfigurationDAO scdao = new SystemConfigurationDAO(con);
				invoiceNumber = scdao.getLastInvoiceNumber();
				// increment it
				invoiceNumber = MerlinUtilities.getNewInvoiceId(invoiceNumber);
				scdao.saveInvoiceNumber(invoiceNumber);
				con.commit();
				// send mail to confirm the order.
				sendMailConfirmingOrder(woBean,iwRegBean.getOrderId(),iwRegBean,invoiceNumber,mapPrices);
			}else if("NOTAUTHED".equals(returnCode) || "REJECTED".equals(returnCode) || "INVALID".equals(returnCode)){
				// Some problem in the data entered by the client. delete the order details.
				wrrdao.deleteWebsiteReportOrdersByOrderId(iwRegBean.getOrderId());
				iwdao.deleteByWebsiteOrderId(iwRegBean.getOrderId());
				wedao.deleteWebsiteOrders(iwRegBean.getOrderId());
				con.commit();
			}
		}catch(Exception e){
			e.printStackTrace();
			ex = e;
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		if(ex != null){
			throw new RuntimeException(ex);
		}
		return mapResponse;
	}

	/**
	 * retrieve a previsouly saved order based on the email id of the customer.
	 */
	public static IWRegistrationBean getIWOrder(String emailAddress){
		IWRegistrationBean iwRegBean = new IWRegistrationBean();
		Connection con=null;
		ConnectionPool pool = null;

		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			// since the user requested a re-registration from the IW login page, that means he has an entry in the
			// IW orders table.
			IndustryWatchDAO iwdao = new IndustryWatchDAO(con);
			IWCustomerDetailsBean iwCusBean = iwdao.getIWCustomerDetails(emailAddress);
			iwRegBean.setChkIndustryWatch(iwCusBean.getSubscriptionType());
			iwRegBean.setTxtEMailAddress(emailAddress);
			iwRegBean.setReRegistration(true);

			WebsiteOrdersDAO wodao = new WebsiteOrdersDAO(con);
			WebsiteOrdersBean wobean = wodao.selectWebsiteOrdersByPK(emailAddress);
			if(wobean != null){
				iwRegBean.setTxtTitle(wobean.getDdSalutation());
				iwRegBean.setTxtFirstName(wobean.getDdFirstName());
				iwRegBean.setTxtSurname(wobean.getDdSurname());
				iwRegBean.setTxtPosition(wobean.getDdPosition());
				iwRegBean.setTxtCompanyName(wobean.getDdCompanyName());
				iwRegBean.setTxtAddress1(wobean.getDdAddressLine1());
				iwRegBean.setTxtAddress2(wobean.getDdAddressLine2());
				iwRegBean.setTxtTown(wobean.getDdTown());
				iwRegBean.setTxtCounty(wobean.getDdCounty());
				iwRegBean.setTxtPostcode(wobean.getDdPostcode());
				iwRegBean.setTxtTelephone(wobean.getTelephone());
				iwRegBean.setTxtCompanyName(wobean.getDdCompanyName());

				iwRegBean.setTxtCardTitle(wobean.getCcSalutation());
				iwRegBean.setTxtCardForename(wobean.getCcFirstName());
				iwRegBean.setTxtCardSurname(wobean.getCcSurname());
				iwRegBean.setTxtCardPosition(wobean.getCcPosition());
				iwRegBean.setTxtCardCompanyName(wobean.getCcCompanyName());
				iwRegBean.setTxtCardAddress1(wobean.getCcAddressLine1());
				iwRegBean.setTxtCardAddress2(wobean.getCcAddressLine2());
				iwRegBean.setTxtCardTown(wobean.getCcTown());
				iwRegBean.setTxtCardCounty(wobean.getCcCounty());
				iwRegBean.setTxtCardPostcode(wobean.getCcPostcode());

				// if there is an entry in the card title value, that indicates that the address & card details
				// are different.
				if(iwRegBean.getTxtCardTitle() != null && iwRegBean.getTxtCardTitle().length() >  0){
					iwRegBean.setRadSameDetails("F");
				}
				//check if there is an associated Merlin Reports entry or not.
				WebsiteReportOrdersDAO wrodao = new WebsiteReportOrdersDAO(con);
				List lstReports = wrodao.getReportTitlesByOrderId(wobean.getWebsiteOrdersId());
				if(lstReports.size() > 0){
					ApplicationResources resource = ApplicationResources.getInstance();
					String separatorString = resource.getProperty("separatorString");

					iwRegBean.setChkMerlinReports("T");

					String reportTitles = "";
					int lstLen = lstReports.size();
					ReportTitlesBean rtbean = null;
	   				for( int i=0;i < lstLen - 1;i++){
	   					rtbean = (ReportTitlesBean)lstReports.get(i);
						reportTitles += rtbean.getReportTitlesId() + separatorString + rtbean.getReportTitle() + separatorString;
					}
	   				rtbean = (ReportTitlesBean)lstReports.get(lstLen - 1);
					reportTitles += rtbean.getReportTitlesId() + separatorString + rtbean.getReportTitle();

					iwRegBean.setHidSelectedReports(reportTitles);


					boolean cdDatabaseRequestedIndicator = wobean.getCdDatabaseRequestedIndicator();
					boolean bookRequestedIndicator = wobean.getBookRequestedIndicator();
					boolean eBookRequestIndicator = wobean.isEBookRequestedIndicator();
					String reportType = null;

					if(eBookRequestIndicator){
						reportType = "eBook";
					}else{
						if(bookRequestedIndicator && cdDatabaseRequestedIndicator){ // both options were ordered.
							reportType = "Book(s) and database(s)";
						}else{
							if(bookRequestedIndicator)
								reportType = "Book(s) only";
							else if(cdDatabaseRequestedIndicator)
								reportType = "Searchable Database(s) only";
						}
					}
					iwRegBean.setRadReportType(reportType);
				}
			}


		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con = null;
		}

		return iwRegBean;
	}

	/**
	 *
	 * @param woBean
	 * @param mapResponse
	 * @param iwRegBean
	 * @param invoiceNumber
	 */
	private static void sendMailConfirmingOrder(WebsiteOrdersBean woBean,int orderId,IWRegistrationBean iwRegBean,String invoiceNumber,Map mapPrices){

    	try{
			ApplicationResources resource = ApplicationResources.getInstance();

			// send a mail to the administrator.
			StringBuffer sbMessageMSAL = new StringBuffer();
			StringBuffer sbMessageCustomer = new StringBuffer();

        	sbMessageCustomer.append("Thank you for your recent order (Order Number: ").append(orderId).append(") which we received ").append(new java.util.Date().toLocaleString()).append("<br>");

			sbMessageMSAL.append("Order Number: ").append(orderId).append("<br>");
			sbMessageMSAL.append("First name: ").append(woBean.getDdFirstName()).append("<BR>Surname: ").append(woBean.getDdSurname());
			sbMessageMSAL.append("<BR>Company Name: ").append(woBean.getDdCompanyName());
			sbMessageMSAL.append("<BR>Postcode : ").append(woBean.getDdPostcode());
			sbMessageMSAL.append("<BR>E-Mail Address: ").append(woBean.getEmailAddress()).append("<BR>");
	  		sbMessageMSAL.append("<BR>Date of order : ").append(new java.util.Date().toLocaleString());

			String[] arrRawReports = null;
			if(woBean.getRawReports() != null){
				arrRawReports = woBean.getRawReports();
				sbMessageMSAL.append("<h4>Merlin Scott Reports</h4>");
				sbMessageCustomer.append("<h4>Merlin Scott Reports</h4>");
				for(int i=0;i< arrRawReports.length-1;i+=2){
			  		sbMessageMSAL.append("<BR>" + arrRawReports[i] + " : " + arrRawReports[i+1]);
			  		sbMessageCustomer.append("<BR>" + arrRawReports[i] + " : " + arrRawReports[i+1]);
		  		}
		  		String formatType = null;
		  		String downloadLinkForEBooks = "";	// applicable only in case of e-books.
		  		if("eBook".equals(iwRegBean.getRadReportType())){
		  			formatType = "Format ordered: E-Book";
					downloadLinkForEBooks = resource.getProperty("merlinBaseURL") + "/mr_downloadEBooks.jsp?token=" + Integer.toHexString(orderId).toUpperCase();
					System.out.println(downloadLinkForEBooks);
		  		}else{
					if("Book(s) only".equals(iwRegBean.getRadReportType())){
						formatType = "Format ordered: Book only";
					}else{
						if("Searchable Database(s) only".equals(iwRegBean.getRadReportType())){
							formatType = "Format ordered: Searchable Database version";
						}else{
							formatType = "Formats ordered: Book & Database versions";
						}
					}
				}
			  	sbMessageMSAL.append("<BR><BR>").append(formatType);
			  	sbMessageCustomer.append("<BR><BR>").append(formatType);
			  	if(downloadLinkForEBooks.length()>0){	// Download links for Merlin e-books.
			  		sbMessageCustomer.append("<P>eBooks are obtained from the <a href ='").append(downloadLinkForEBooks).append("'>").append("report download page").append("</a>");
			  		sbMessageCustomer.append(".  If the report is still being prepared (you will see a message to this effect) simply return to report download page later,");
			  		sbMessageCustomer.append(" the new report will have been compiled and will be available for you to download.<P/>");

					sbMessageCustomer.append("The report file size can be up to 56MB so it will take up to five ");
					sbMessageCustomer.append("minutes depending on your internet connection speed to successfully ");
					sbMessageCustomer.append("download.  Settings you have got in your browser will determine whether ");
					sbMessageCustomer.append("the download manager shows you the progress of the file transfer or the ");
					sbMessageCustomer.append("browser window may just stay blank until the file is downloaded ");
					sbMessageCustomer.append("completely,  please be patient while this process is underway.  You ");
					sbMessageCustomer.append("should save a copy of the report file on your hard disk once the ");
					sbMessageCustomer.append("download is complete.<p/>");

					sbMessageCustomer.append("You will require a copy of Adobe Acrobat Reader (free from <a href='www.adobe.com'>www.adobe.com</a>) to view the report contents.");

					sbMessageMSAL.append("<BR>E-Books download link : <a href ='").append(downloadLinkForEBooks).append("'>").append(downloadLinkForEBooks).append("</a>");
				}
			}

			if(woBean.isIndustryWatchReports()){
				// default password is "iw" + <orderId>. The total string shouldn't be bigger than 12 chars.
				String password = "iw" + orderId;
				if(password.length() > 12){
					password = password.substring(0,12);
				}

				// Add login information.
				sbMessageMSAL.append("<h4>Industry Watch Service</h4>");

				sbMessageCustomer.append("<h4>Industry Watch Service</h4>");
				sbMessageCustomer.append("Your login details are as follows : <br>");

				String loginPage = resource.getProperty("iwLoginPage");

				sbMessageCustomer.append("Industry Watch Login Page : <a href='").append(loginPage).append("'>").append(loginPage).append("</a><br>");

				sbMessageMSAL.append("User Name : ").append(woBean.getEmailAddress()).append("<br>");
				sbMessageMSAL.append("Password : ").append(password).append("<br>");

				sbMessageCustomer.append("User Name : ").append(woBean.getEmailAddress()).append("<br>");
				sbMessageCustomer.append("Password : ").append(password).append("<br>");

				sbMessageCustomer.append("You can change the password to something more convenient using the help menu once you are logged on.<br>");
			}

			PDFReportGenerator pdfGenerator = new PDFReportGenerator();

			Map mapData = new HashMap();
			mapData.put("WebsiteOrdersBean",woBean);
			mapData.put("invoiceNumber",invoiceNumber);
			mapData.put("iwRegBean",iwRegBean);

 			byte[] invoiceData = pdfGenerator.generateInvoice(mapData,mapPrices);

			if(arrRawReports != null && arrRawReports.length > 0 &&  ! "eBook".equals(iwRegBean.getRadReportType())){
				if(arrRawReports.length > 1){
					sbMessageCustomer.append("<br>We will send the reports shortly.");
				}else{
					sbMessageCustomer.append("<br>We will send the report shortly.");
				}
			}


			sbMessageMSAL.append("<br><br>Niel Dalrymple<br>Merlin Scott Associates Limited");
			sbMessageCustomer.append("<br><br>Niel Dalrymple<br>Merlin Scott Associates Limited");

			String mailTo  = resource.getProperty("mail.to");
			String mailFrom = woBean.getEmailAddress();
			String subject =  "Order Placed through Merlin Scott Associates Ltd";

			// Send mail to MSAL
			MailSender.sendMail(mailFrom,mailTo,subject,sbMessageMSAL.toString(),"text/html",invoiceData,"application/pdf","invoice.pdf");

			// depending on the type of purchase, the sender address needs to change.
			if(woBean.isIndustryWatchReports() || woBean.isPdfReportPurchase()){
				mailFrom = "Niel.Dalrymple@industrywatch.co.uk";
			}else{
				mailFrom = "Niel.Dalrymple@merlinscottassociates.co.uk";
			}

			mailTo 	 = woBean.getEmailAddress();
			//Send mail to customer.
			MailSender.sendMail(mailFrom,mailTo,subject,sbMessageCustomer.toString(),"text/html",invoiceData,"application/pdf","invoice.pdf");
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	public static void storeLandingPageVisitDetailsFromEmailLink(int reportNumber, String companyNumber){
		Connection con=null;
		ConnectionPool pool = null;

		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();
			con.setAutoCommit(false);
			
			// Save the details of the visit.
			WebsiteEnquiriesDAO dao = new WebsiteEnquiriesDAO(con);
			dao.storeLandingPageVisitDetailsFromEmailLink(reportNumber, companyNumber);
			con.commit();
			
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con = null;
		}
	}
	
	public static void storeEmailAddressForFreeMSALReport(final String emailAddress,final int reportNumber, final String companyNumber){
		Connection con=null;
		ConnectionPool pool = null;

		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();
			con.setAutoCommit(false);
			
			// Save the email details for the user, and send an email with the free report download link.
			EmailDataBean bean = new EmailDataBean(companyNumber, reportNumber, emailAddress, 0);
			WebsiteEnquiriesDAO dao = new WebsiteEnquiriesDAO(con);
			int recordId = dao.addEmailAddressForFreeReport(bean);
			con.commit();
			
			//TODO : Create the download URL.
			ApplicationResources resource = ApplicationResources.getInstance();
			final String downloadUrl = resource.getProperty("merlinBaseURL") + "/MSALFreeReportDownload.jsp?token=" + Integer.toHexString(recordId).toUpperCase();
			
			// send the mail using a separate thread.
			new Thread(new Runnable(){public void run(){sendEmailWithFreeReportDownloadLink(emailAddress, reportNumber, companyNumber, downloadUrl);}}).start();
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con = null;
		}
	}
	
	private static void sendEmailWithFreeReportDownloadLink(String emailAddress, int reportNumber, String companyNumber, String downloadUrl){
		try{
			
			
			ApplicationResources resource = ApplicationResources.getInstance();
			String mailTo  = emailAddress;
			String mailFrom = "Niel.Dalrymple@merlinscottassociates.co.uk";
			String subject =  resource.getProperty("freereportdownloandemail.subject");
			
			String templateFileLocation = resource.getProperty("freereportdownloandemail.mailtemplatefilelocation");
			String parameterName = resource.getProperty("freereportdownloandemail.reporturlparametername");
			
			FileReader fr = new FileReader(templateFileLocation);
			BufferedReader br = new BufferedReader(fr);
			StringBuilder sb = new StringBuilder();
	        String line = br.readLine();
	        while (line != null) {
	            sb.append(line);
	            sb.append("\n");
	            line = br.readLine();
	        }
	        fr.close();
	        
			String message = sb.toString();
			
			message = message.replaceAll(parameterName, downloadUrl);
			
			System.out.println("Sending the message : " + message);
			// Send mail to MSAL
			MailSender.sendMail(mailFrom,mailTo,subject,message);

		}catch(Exception e){
			System.out.println("Unable to send free report downloand email to " + emailAddress);
			e.printStackTrace();
		}
	}
}